package org.shams.phonebook.utils;

import org.apache.commons.dbcp.BasicDataSource;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @auther <a href="m.h.shams@gmail.com">M. H. Shamsi </a>
 * @since Aug 17, 2008
 */
public class DataBaseUtils {
    BasicDataSource datasource;

    public DataBaseUtils(BasicDataSource datasource) {
        this.datasource = datasource;
        this.createDatabase();
    }

    public void createDatabase() throws RuntimeException {
        try {
            Connection connection = init();
            ddlscripts(connection);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void clearDatabase() throws RuntimeException {
        try {
            Connection connection = init();
            dmlscripts(connection);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    private Connection init() throws Exception {
        return datasource.getConnection();
    }

    private void dmlscripts(Connection connection) throws Exception {
        Statement statement = connection.createStatement();
        statement.executeUpdate("delete from _item");
        statement.executeUpdate("delete from _user");
        connection.commit();
        statement.execute("SHUTDOWN");
        statement.close();
    }

    private void ddlscripts(Connection connection) throws Exception {
        Statement statement = connection.createStatement();
        
        // first, check if _item not exist, then create it.
        try {
            statement.execute("select count(*) from _item");
        } catch (SQLException e) {
            statement.executeUpdate("create table _item(" +
                    "id      int generated by default as identity (start with 1, increment by 1) not null primary key," +
                    "name    varchar(40) not null," +
                    "family  varchar(40) not null," +
                    "phone   varchar(15) not null," +
                    "mobile  varchar(15)," +
                    "mail  varchar(40)," +
                    "address varchar(200)" +
                    ")");
        }

        // first, check if _item not exist, then create it.
        try {
            statement.execute("select count(*) from _user");
        } catch (SQLException e) {
            statement.executeUpdate("create cached table _user(" +
                    "id        int generated by default as identity (start with 1, increment by 1) not null primary key," +
                    "name      varchar(100) not null," +
                    "username  varchar(40)  not null," +
                    "password  varchar(40)  not null," +
                    "constraint uuser unique (username)" +
                    ")");

            connection.commit();
            statement.execute("SHUTDOWN");
            statement.close();
        }
    }
}
